﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace FortuneCookie.EPiServer.SearchAndReplace.Data
{

    /// <summary>
    /// Data access base class
    /// </summary>
    internal abstract class DataAccessBase : IDisposable
    {

        #region Members

        private bool _disposed;
        protected delegate IList BuildEntitiesDelegate(IDataReader reader);

        #endregion Members

        #region Properties

        /// <summary>
        /// Gets or sets the connection
        /// </summary>
        protected DbConnection Connection { get; set; }

        #endregion Properties

        #region Methods

        /// <summary>
        /// Loads entities using the result set of a stored procedure
        /// </summary>
        /// <typeparam name="T">Entity collection type</typeparam>
        /// <param name="procedureName">Stored procedure name</param>
        /// <param name="buildEntitiesDelegate">Build entities delegate</param>
        /// <returns>T</returns>
        protected T Load<T>(string procedureName, BuildEntitiesDelegate buildEntitiesDelegate) 
            where T : IList, new()
        {
            return Load<T>(procedureName, null, buildEntitiesDelegate);
        }

        /// <summary>
        /// Loads entities using the result set of a stored procedure
        /// </summary>
        /// <typeparam name="T">Entity collection type</typeparam>
        /// <param name="procedureName">Stored procedure name</param>
        /// <param name="parameters">Data parameters</param>
        /// <param name="buildEntitiesDelegate">Build entities delegate</param>
        /// <returns>T</returns>
        protected T Load<T>(string procedureName, List<DbParameter> parameters, BuildEntitiesDelegate buildEntitiesDelegate) 
            where T : IList, new()
        {
            T entities = new T();
            DbCommand command = null;
            DbDataReader reader = null;

            try
            {
                command = CreateStoredProcedureCommand(procedureName);

                if (parameters != null && parameters.Count > 0)
                {
                    foreach (DbParameter parameter in parameters)
                        command.Parameters.Add(parameter);
                }

                OpenConnection();
                reader = command.ExecuteReader();
                entities = (T)buildEntitiesDelegate.Method.Invoke(this, new object[] {reader});
                ReleaseReader(reader);
            }
            finally
            {
                ReleaseReader(reader);
                ReleaseCommand(command);
            }

            return entities;
        }

        /// <summary>
        /// Executes a non query using a stored procedure
        /// </summary>
        /// <param name="procedureName">Stored procedure name</param>
        /// <param name="parameters">Data parameters</param>
        protected void ExecuteNonQuery(string procedureName, List<DbParameter> parameters)
        {
            DbCommand command = null;

            try
            {
                command = CreateStoredProcedureCommand(procedureName);

                if (parameters != null && parameters.Count > 0)
                {
                    foreach (DbParameter parameter in parameters)
                        command.Parameters.Add(parameter);
                }

                OpenConnection();
                command.ExecuteNonQuery();
                CloseConnection();
            }
            finally
            {
                CloseConnection();
                ReleaseCommand(command);
            }
        }

        /// <summary>
        /// Executes a non query the supplied command text
        /// </summary>
        /// <param name="commandText">Command text</param>
        protected void ExecuteNonQuery(string commandText)
        {
            DbCommand command = null;

            try
            {
                command = CreateCommand(commandText);
                OpenConnection();
                command.ExecuteNonQuery();
                CloseConnection();
            }
            finally
            {
                CloseConnection();
                ReleaseCommand(command);
            }
        }

        /// <summary>
        /// Executes a scalar query
        /// </summary>
        /// <param name="commandText">Command text</param>
        /// <typeparam name="T"></typeparam>
        protected T ExecuteScalar<T>(string commandText)
        {
            T value = default(T);
            DbCommand command = null;

            try
            {
                command = CreateCommand(commandText);
                OpenConnection();
                value = (T)command.ExecuteScalar();
                CloseConnection();
            }
            finally
            {
                CloseConnection();
                ReleaseCommand(command);
            }

            return value;
        }

        /// <summary>
        /// Releases a data reader
        /// </summary>
        /// <param name="reader">Data reader</param>
        protected void ReleaseReader(DbDataReader reader)
        {
            if (reader != null)
            {
                if (!reader.IsClosed)
                    reader.Close();

                reader.Dispose();
            }

            CloseConnection();
        }

        /// <summary>
        /// Creates a new DbCommand
        /// </summary>
        /// <param name="procedureName">Stored procedure name</param>
        /// <returns></returns>
        protected DbCommand CreateStoredProcedureCommand(string procedureName)
        {
            DbCommand command = Connection.CreateCommand();
            command.CommandText = procedureName;
            command.CommandType = CommandType.StoredProcedure;
            return command;
        }

        /// <summary>
        /// Creates a new DbCommand
        /// </summary>
        /// <param name="commandText">Command text</param>
        /// <returns></returns>
        protected DbCommand CreateCommand(string commandText)
        {
            DbCommand command = Connection.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = CommandType.Text;
            return command;
        }

        /// <summary>
        /// Releases a command
        /// </summary>
        /// <param name="command">DbCommand</param>
        protected void ReleaseCommand(DbCommand command)
        {
            if (command != null)
                command.Dispose();
        }

        /// <summary>
        /// Opens the database connection.
        /// </summary>
        protected void OpenConnection()
        {
            if (Connection == null) 
                return;

            if (Connection.State != ConnectionState.Open)
                Connection.Open();
        }

        /// <summary>
        /// Closes the database connection.
        /// </summary>
        protected void CloseConnection()
        {
            if (Connection == null) 
                return;

            if (Connection.State != ConnectionState.Closed)
                Connection.Close();
        }

        #endregion Methods

        #region IDisposable members

        /// <summary>
        /// Disposes of the current class.
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        /// <summary>
        /// Disposes all managed and unmanaged resources in this class.
        /// </summary>
        /// <param name="disposing">True if disposing, otherwise false.</param>
        private void Dispose(bool disposing)
        {
            // Check to see if dispose has already been called
            if (!_disposed)
                return;

            if (Connection != null)
            {
                CloseConnection();
                Connection.Dispose();
            }

            _disposed = true;
        }

        #endregion IDisposable members

    }
}
